import TableCreator from './TableCreator';
import TableIndex from './TableIndex';
import {get,exec,transaction} from './SQLDriver.js';


const undef = void 0;
/**
 * 表执行器
 * @author xueyanjun
 */
class TableExecutor{
	constructor(database,tableName){
		this.tableName = tableName;
		this.database = database;
		this.index = new TableIndex(database,tableName);
		this.trans = {
			// 开启事务
			begin:function(){
				return transaction.begin(this.database);
			},
			// 提交事务
			commit:function(){
				return transaction.commit(this.database);
			},
			// 回滚事务
			rollback:function(){
				return transaction.rollback(this.database);
			},
		}
	}
	
	/**
	 * 建表
	 * @param {Object} configFunction 配置回调
	 */
	create(configFunction){
		const tableDef = new TableCreator(this.tableName);
		configFunction(tableDef);
		const sqlstr = tableDef.getSQL();
		return exec(this.database,sqlstr);
	}
	
	/**
	 * 删除表
	 */
	drop(){
		return exec(this.database,'DROP TABLE '+this.tableName);
	}
	
	/**
	 * 判断表是否存在
	 * @returns {Boolean} true代表存在，false代表不存在
	 */
	async exists(){
		const res =  await get(this.database,'SELECT count(*) as c FROM sqlite_master WHERE type="table" AND name = "'+this.tableName+'"');
		return res[0].c == 1;
	}
	
	/**
	 * 往表中插入数据
	 * @param {object} colVals 数据形式例如 {name:'bob',age:12} 或者 [{name:'bob',age:12}]
	 */
	insert(...colVals){
		if(!colVals.flat)return 
		let _colVals = colVals.flat();
		if(_colVals.length === 0){
			return;
		}
		
		const cols = Object.keys(_colVals[0]);
		let sql = `INSERT INTO ${this.tableName}(${cols.toString()}) `;
		
		if(_colVals.length === 1){
			const cv = _colVals[0];
			const insertVals = [];
			for (let i = 0; i < cols.length; i++) {
				 const c = cols[i];
				 const v = cv[c];
				 insertVals.push("'"+v+"'");
			}
			const valuesSQL = insertVals.join(',');
			sql += 'VALUES('+valuesSQL+')';
		}else{
			const insertValSqls = [];
			for (let i = 0; i < _colVals.length; i++) {
				const cv =  _colVals[i];
				const insertVals = [];
				for (let j = 0; j < cols.length; j++) {
					 const c = cols[j];
					 const v = cv[c];
					 insertVals.push("'"+v+"'");
				}
				insertValSqls.push('SELECT ' + insertVals.join(','))
			}
			const valuesSQL = insertValSqls.join(' UNION ALL ');
			sql += valuesSQL;
		}
		
		return exec(this.database,sql);
	}
	
	/**
	 * 查询得到最后新增的id
	 */
	selectLastId(){
		return get(this.database,'SELECT last_insert_rowid() AS lastId FROM ' + this.tableName).then((res)=>{
			return res[0].lastId;
		});
	}
	
	/**
	 * 从表中查找数据
	 * @param {Object} options {field:要查询的列，默认*代表所有,where:查询条件,groupBy:分组聚合,orderByASC:正排,orderByDESC:倒排,limit:限制查询行数,having:group后过滤}
	 */
	select(options){
		const {field=['*'],where,groupBy,orderByASC,orderByDESC,limit,having} = options;
		let selectSQL = 'SELECT '+field.toString()+ ' FROM ' + this.tableName;
		
		if(where != null){
			selectSQL += this._buildWhere(where);
		}
		if(groupBy != null && groupBy.length > 0){
			selectSQL += ' GROUP BY '+groupBy.toString();
		}
		if(having != null){
			selectSQL += this._buildHaving(having);
		}
		if(orderByASC != null && orderByASC.length > 0){
			selectSQL += ' ORDER BY '+orderByASC.toString() + ' ASC';
		}
		if(orderByDESC != null && orderByDESC.length > 0){
			selectSQL += ' ORDER BY '+orderByDESC.toString() + ' DESC';
		}
		if(limit != null && limit.length > 0){
			selectSQL += ' LIMIT '+limit[1]+' OFFSET ' + limit[0];
		}
		return get(this.database,selectSQL);
	}
	
	/**
	 * 更新数据
	 *  @param {Object|Array} updates {set:更新字段与值,where:更新条件} 
	 */
	update(...updates){
		const _updates = updates.flat();
		const _updateSqls = [];
		for (let i = 0; i < _updates.length; i++) {
			const updateobj = _updates[i];
			if(typeof updateobj === 'string'){
				_updateSqls.push(updateobj);
				continue;
			}
			const {set,where} = updateobj;
			let setSQL =  'UPDATE '+this.tableName + ' SET ';
			// 当设置参数为对象时
			if(typeof set === 'object'){
				const cols = Object.keys(set);
				const setVals = [];
				for (let i = 0; i < cols.length; i++) {
					const c = cols[i];
					const v = set[c];
					if(v === undef){
						continue;
					}
					if(typeof v === 'string'){
						setVals.push(c + '=' + "'"+v+"'");
					}else{
						setVals.push(c + '=' + v);
					}
				}
				setSQL += setVals.join(',');
			}
			// 当设置参数为string时
			else if(typeof set === 'string'){
				setSQL += set
			}
			const whereSQL = this._buildWhere(where);
			_updateSqls.push(setSQL + whereSQL);
		}
		if(_updateSqls.length > 0){
			return exec(this.database,_updateSqls);
		}
	}
	
	/**
	 * 删除数据
	 */
	delete(where={}){
		const delSQL = 'DELETE FROM '+this.tableName;
		const whereSQL = this._buildWhere(where);
		return exec(this.database,delSQL + whereSQL);
	}
	
	_buildHaving(having={}){
		return ' HAVING 1=1' + this._buildCondition(having);
	}
	_buildWhere(where={}){
		return ' WHERE 1=1' + this._buildCondition(where);
	}
	
	_buildCondition(condition){
		const {eq,notEq,lt,gt,le,lg,notIn,like,glob,between,notNull} = condition;
		const IN = condition.in;
		let conditionSQL = '';
		if(eq != null){
			const cols = Object.keys(eq);
			for (let i = 0; i < cols.length; i++) {
				const c = cols[i];
				const v = eq[c];
				if(v === undef){
					continue;
				}
				if(typeof v === 'string'){
					conditionSQL += ' AND '+ c + '=' + "'"+v+"'";
				}else{
					conditionSQL += ' AND '+ c + '=' + v;
				}
			}
		}
		
		if(notEq != null){
			const cols = Object.keys(notEq);
			for (let i = 0; i < cols.length; i++) {
				const c = cols[i];
				const v = notEq[c];
				if(v === undef){
					continue;
				}
				if(typeof v === 'string'){
					conditionSQL += ' AND '+ c + '!=' + "'"+v+"'";
				}else{
					conditionSQL += ' AND '+ c + '!=' + v;
				}	
			}
		}
		
		if(lt != null){
			const cols = Object.keys(lt);
			for (let i = 0; i < cols.length; i++) {
				const c = cols[i];
				const v = lt[c];
				if(v === undef){
					continue;
				}
				conditionSQL += ' AND '+ c + '<' + v
			}
		}
		
		if(gt != null){
			const cols = Object.keys(gt);
			for (let i = 0; i < cols.length; i++) {
				const c = cols[i];
				const v = gt[c];
				if(v === undef){
					continue;
				}
				conditionSQL += ' AND '+ c + '>' + v
			}
		}
		
		if(le != null){
			const cols = Object.keys(le);
			for (let i = 0; i < cols.length; i++) {
				const c = cols[i];
				const v = le[c];
				if(v === undef){
					continue;
				}
				conditionSQL += ' AND '+ c + '<=' + v
			}
		}
		
		if(lg != null){
			const cols = Object.keys(lg);
			for (let i = 0; i < cols.length; i++) {
				const c = cols[i];
				const v = lg[c];
				if(v === undef){
					continue;
				}
				conditionSQL += ' AND '+ c + '>=' + v
			}
		}
		
		if(like != null){
			const cols = Object.keys(like);
			for (let i = 0; i < cols.length; i++) {
				const c = cols[i];
				const v = like[c];
				if(v === undef){
					continue;
				}
				conditionSQL += " AND "+ c + " LIKE " + "'" +  v + "'"
			}
		}
		
		if(glob != null){
			const cols = Object.keys(glob);
			for (let i = 0; i < cols.length; i++) {
				const c = cols[i];
				const v = glob[c];
				if(v === undef){
					continue;
				}
				conditionSQL += ' AND '+ c + ' GLOB ' + v
			}
		}
		
		if(between != null){
			const cols = Object.keys(between);
			for (let i = 0; i < cols.length; i++) {
				const c = cols[i];
				const v = glob[c];
				conditionSQL += ' AND '+ c + ' BETWEEN ' + v[0] + ' AND ' + v[1];
			}
		}
		
		if(notNull != null){
			const cols = notNull;
			for (let i = 0; i < cols.length; i++) {
				const c = cols[i];
				conditionSQL += ' AND '+ c + ' IS NOT NULL';
			}
		}
		
		if(IN != null){
			const cols = Object.keys(IN);
			for (let i = 0; i < cols.length; i++) {
				const c = cols[i];
				const vs = IN[c];
				const valIns = [];
				for (let j = 0; j < vs.length; j++) {
					const v = vs[j];
					if(v === undef){
						continue;
					}
					if(typeof v === 'string'){
						valIns.push("'"+ v +"'");
					}else{
						valIns.push(v);
					}
				}
				const sql = valIns.join(',');
				conditionSQL += ' AND '+ c + ' IN(' + sql +')';
			}
		}
		return conditionSQL;
	}
}

export default TableExecutor;